﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace WFL.Data.SqlClient
{
    public class SqlOperation
    {

        #region 静态方法

        public static int CommandTimeout
        {
            get;
            set;
        }

        static SqlOperation()
        {
            SqlOperation.CommandTimeout = -1;
        }


        #region ExecuteNonQuery

        public static int ExecuteNonQuery(string sql, SqlConnection connection)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, null);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, SqlConnection connection)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, null);
        }

        public static int ExecuteNonQuery(string sql, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, null);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteNonQuery(sql, commandType, connection, transaction, null);
        }

        public static int ExecuteNonQuery(string sql, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, null, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, commandType, connection, null, parameters);
        }

        public static int ExecuteNonQuery(string sql, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static int ExecuteNonQuery(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (CommandTimeout >= 0)
            {
                comm.CommandTimeout = CommandTimeout;
            }
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            try
            {
                return comm.ExecuteNonQuery();
            }
            finally
            {
                if (parameters != null)
                {
                    comm.Parameters.Clear();
                }
                comm.Dispose();
            }
        }

        #endregion


        #region ExecuteReader

        public static SqlDataReader ExecuteReader(string sql, SqlConnection connection)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, null);
        }

        public static SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlConnection connection)
        {
            return ExecuteReader(sql, commandType, connection, null, null);
        }

        public static SqlDataReader ExecuteReader(string sql, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, null);
        }

        public static SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteReader(sql, commandType, connection, transaction, null);
        }

        public static SqlDataReader ExecuteReader(string sql, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, null, parameters);
        }

        public static SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteReader(sql, commandType, connection, null, parameters);
        }

        public static SqlDataReader ExecuteReader(string sql, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            return ExecuteReader(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static SqlDataReader ExecuteReader(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (CommandTimeout >= 0)
            {
                comm.CommandTimeout = CommandTimeout;
            }
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            try
            {
                return comm.ExecuteReader();
            }
            finally
            {
                if (parameters != null)
                {
                    comm.Parameters.Clear();
                    comm.Dispose();
                }
            }
        }

        #endregion


        #region ExecuteScalar

        public static object ExecuteScalar(string sql, SqlConnection connection)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, null);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, SqlConnection connection)
        {
            return ExecuteScalar(sql, commandType, connection, null, null);
        }

        public static object ExecuteScalar(string sql, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, null);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteScalar(sql, commandType, connection, transaction, null);
        }

        public static object ExecuteScalar(string sql, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, null, parameters);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteScalar(sql, commandType, connection, null, parameters);
        }

        public static object ExecuteScalar(string sql, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static object ExecuteScalar(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (CommandTimeout >= 0)
            {
                comm.CommandTimeout = CommandTimeout;
            }
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            try
            {
                return comm.ExecuteScalar();
            }
            finally
            {
                if (parameters != null)
                {
                    comm.Parameters.Clear();
                }
            }
        }

        #endregion


        #region ExecuteDataSet

        public static DataSet ExecuteDataSet(string sql, SqlConnection connection)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, null);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, SqlConnection connection)
        {
            return ExecuteDataSet(sql, commandType, connection, null, null);
        }

        public static DataSet ExecuteDataSet(string sql, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, null);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction)
        {
            return ExecuteDataSet(sql, commandType, connection, transaction, null);
        }

        public static DataSet ExecuteDataSet(string sql, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, null, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, SqlConnection connection, SqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, commandType, connection, null, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, connection, transaction, parameters);
        }

        public static DataSet ExecuteDataSet(string sql, CommandType commandType, SqlConnection connection, SqlTransaction transaction, SqlParameter[] parameters)
        {
            SqlCommand comm = new SqlCommand();
            comm.Connection = connection;
            comm.CommandText = sql;
            comm.CommandType = commandType;
            comm.Transaction = transaction;
            if (CommandTimeout >= 0)
            {
                comm.CommandTimeout = CommandTimeout;
            }
            if (parameters != null)
            {
                comm.Parameters.AddRange(parameters);
            }
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(comm);
                DataSet result = new DataSet();
                adapter.Fill(result);
                return result;
            }
            finally
            {
                if (parameters != null)
                {
                    comm.Parameters.Clear();
                }
            }
        }

        #endregion

        #endregion


        public string ConnectionString
        {
            get;
            set;
        }

        public SqlOperation()
        {
            
        }

        public SqlOperation(string connectionString)
            : this()
        {
            
        }

        protected virtual SqlConnection GetContextConnection()
        {
            return null;
        }

        protected virtual SqlTransaction GetContextTransaction()
        {
            return null;
        }

        private SqlConnection GetConnection(out bool newConn)
        {
            newConn = false;
            SqlConnection connection = GetContextConnection();
            if (connection == null)
            {
                connection = new SqlConnection(ConnectionString);
                connection.Open();
                newConn = true;
            }
            return connection;
        }

        private SqlTransaction BeginTransaction(SqlConnection connection, IsolationLevel iso, out bool newTrans)
        {
            newTrans = false;
            SqlTransaction transaction = GetContextTransaction();
            if (transaction != null)
            {
                if (transaction.IsolationLevel != iso)
                {
                    throw new Exception("上下文中存在不同隔离级别的事务。");
                }
            }
            else
            {
                transaction = connection.BeginTransaction(iso);
                newTrans = true;
            }
            return transaction;
        }


        #region ExecuteNonQuery

        public int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, CommandType.Text, (SqlParameter[])null);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType)
        {
            return ExecuteNonQuery(sql, commandType, (SqlParameter[])null);
        }

        public int ExecuteNonQuery(string sql, SqlParameter[] parameters)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            try
            {
                return ExecuteNonQuery(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (newConn)
                {
                    connection.Close();
                }
            }
        }

        public int ExecuteNonQuery(string sql, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, CommandType.Text, null, iso);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, commandType, null, iso);
        }

        public int ExecuteNonQuery(string sql, SqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteNonQuery(sql, CommandType.Text, parameters, iso);
        }

        public int ExecuteNonQuery(string sql, CommandType commandType, SqlParameter[] parameters, IsolationLevel iso)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            SqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                int result = ExecuteNonQuery(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (newConn)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteScalar

        public object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, CommandType.Text, (SqlParameter[])null);
        }

        public object ExecuteScalar(string sql, CommandType commandType)
        {
            return ExecuteScalar(sql, commandType, (SqlParameter[])null);
        }

        public object ExecuteScalar(string sql, SqlParameter[] parameters)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters);
        }

        public object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            try
            {
                return ExecuteScalar(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (newConn)
                {
                    connection.Close();
                }
            }
        }

        public object ExecuteScalar(string sql, IsolationLevel iso)
        {
            return ExecuteScalar(sql, CommandType.Text, null, iso);
        }

        public object ExecuteScalar(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteScalar(sql, commandType, null, iso);
        }

        public object ExecuteScalar(string sql, SqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteScalar(sql, CommandType.Text, parameters, iso);
        }

        public object ExecuteScalar(string sql, CommandType commandType, SqlParameter[] parameters, IsolationLevel iso)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            SqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                object result = ExecuteScalar(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (newConn)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteDataSet

        public DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, CommandType.Text, (SqlParameter[])null);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType)
        {
            return ExecuteDataSet(sql, commandType, (SqlParameter[])null);
        }

        public DataSet ExecuteDataSet(string sql, SqlParameter[] parameters)
        {
            return ExecuteDataSet(sql, CommandType.Text, parameters);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            try
            {
                return ExecuteDataSet(sql, commandType, connection, GetContextTransaction(), parameters);
            }
            finally
            {
                if (newConn)
                {
                    connection.Close();
                }
            }
        }

        public DataSet ExecuteDataSet(string sql, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, CommandType.Text, null, iso);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, commandType, null, iso);
        }

        public DataSet ExecuteDataSet(string sql, SqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteDataSet(sql, CommandType.Text, parameters, iso);
        }

        public DataSet ExecuteDataSet(string sql, CommandType commandType, SqlParameter[] parameters, IsolationLevel iso)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            SqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                DataSet result = ExecuteDataSet(sql, commandType, connection, transaction, parameters);
                if (newTrans)
                {
                    transaction.Commit();
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                throw;
            }
            finally
            {
                if (newConn)
                {
                    connection.Close();
                }
            }
        }

        #endregion


        #region ExecuteReaderExtension

        public SqlDataReaderExtension ExecuteReaderExtension(string sql)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, null);
        }

        public SqlDataReaderExtension ExecuteReaderExtension(string sql, SqlParameter[] parameters)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, parameters);
        }

        public SqlDataReaderExtension ExecuteReaderExtension(string sql, CommandType commandType, SqlParameter[] parameters)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            try
            {
                SqlDataReader reader = SqlOperation.ExecuteReader(sql, commandType, connection, GetContextTransaction(), parameters);
                SqlDataReaderExtension result = new SqlDataReaderExtension(reader);
                if (newConn)
                {
                    result.Connection = connection;
                }
                return result;
            }
            catch
            {
                if (newConn)
                {
                    connection.Close();
                }
                throw;
            }
        }

        public SqlDataReaderExtension ExecuteReaderExtension(string sql, IsolationLevel iso)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, null, iso);
        }

        public SqlDataReaderExtension ExecuteReaderExtension(string sql, SqlParameter[] parameters, IsolationLevel iso)
        {
            return ExecuteReaderExtension(sql, CommandType.Text, parameters, iso);
        }

        public SqlDataReaderExtension ExecuteReaderExtension(string sql, CommandType commandType, SqlParameter[] parameters, IsolationLevel iso)
        {
            bool newConn = false;
            SqlConnection connection = GetConnection(out newConn);
            SqlTransaction transaction = null;
            bool newTrans = false;
            try
            {
                transaction = BeginTransaction(connection, iso, out newTrans);
                SqlDataReader reader = SqlOperation.ExecuteReader(sql, commandType, connection, transaction, parameters);
                SqlDataReaderExtension result = new SqlDataReaderExtension(reader);
                if (newTrans)
                {
                    result.Transaction = transaction;
                }
                if (newConn)
                {
                    result.Connection = connection;
                }
                return result;
            }
            catch
            {
                if (transaction != null
                    && newTrans)
                {
                    transaction.Rollback();
                }
                if (newConn)
                {
                    connection.Close();
                }
                throw;
            }
        }

        #endregion


    }
}
